﻿using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint.WebControls;
using System;
using System.Web;
using System.Data;
using System.IO;
using System.Security;
using System.Xml;
using Microsoft.SharePoint;
//using Microsoft.SharePoint.WebControls;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
using Microsoft.SharePoint.Utilities;
using System.Text;

namespace Presentation.GuiaDev.WebPart.VWPExportImprimirFiltroIngles
{
    public partial class VWPExportImprimirFiltroInglesUserControl : UserControl
    {
        string estadoPesquisa = string.Empty;
        string municipioPesquisa = string.Empty;
        string estado = string.Empty;
        string municipio = string.Empty;
        string setorEmpresa = string.Empty;
        string porteEmpresa = string.Empty;
        string faturamentoEmpresa = string.Empty;
        string modalidadeEmpresa = string.Empty;
        string objetivoEmpresa = string.Empty;
        string perfilEmpresa = string.Empty;
        string estadoPesquisaIngles = string.Empty;
        string municipioPesquisaIngles = string.Empty;
        string estadoIngles = string.Empty;
        string municipioIngles = string.Empty;
        string setorEmpresaIngles = string.Empty;
        string porteEmpresaIngles = string.Empty;
        string faturamentoEmpresaIngles = string.Empty;
        string modalidadeEmpresaIngles = string.Empty;
        string objetivoEmpresaIngles = string.Empty;
        string perfilEmpresaIngles = string.Empty;
        string aprovacao = string.Empty;
        SPSite siteColl = SPContext.Current.Site;
        SPWeb site = SPContext.Current.Web;

        protected void Page_Load(object sender, EventArgs e)
        {

            estadoPesquisa = (String)Session["estadoPesquisar"] != null ? (String)Session["estadoPesquisar"].ToString() : "";
            municipioPesquisa = (String)Session["municipioPesquisar"] != null ? (String)Session["municipioPesquisar"].ToString() : "";
            estado = (String)Session["estadoEmpresa"] != null ? (String)Session["estadoEmpresa"].ToString() : "";
            municipio = (String)Session["municipioEmpresa"] != null ? (String)Session["municipioEmpresa"].ToString() : "";
            setorEmpresa = (String)Session["setorEmpresa"] != null ? (String)Session["setorEmpresa"].ToString() : "";
            porteEmpresa = (String)Session["porteEmpresa"] != null ? (String)Session["porteEmpresa"].ToString() : "";
            faturamentoEmpresa = (String)Session["faturamentoEmpresa"] != null ? (String)Session["faturamentoEmpresa"].ToString() : "";
            modalidadeEmpresa = (String)Session["modalidadeEmpresa"] != null ? (String)Session["modalidadeEmpresa"].ToString() : "";
            objetivoEmpresa = (String)Session["objetivoEmpresa"] != null ? (String)Session["objetivoEmpresa"].ToString() : "";
            perfilEmpresa = (String)Session["perfilEmpresa"] != null ? (String)Session["perfilEmpresa"].ToString() : "";

            estadoPesquisaIngles = Request.Params["estadoPesquisar"] != null ? Request.Params["estadoPesquisar"].ToString() : "";
            municipioPesquisaIngles = Request.Params["municipioPesquisar"] != null ? Request.Params["municipioPesquisar"].ToString() : "";
            estadoIngles = Request.Params["estadoEmpresa"] != null ? Request.Params["estadoEmpresa"].ToString() : "";
            municipioIngles = Request.Params["municipioEmpresa"] != null ? Request.Params["municipioEmpresa"].ToString() : "";
            setorEmpresaIngles = Request.Params["setorEmpresa"] != null ? Request.Params["setorEmpresa"].ToString() : "";
            porteEmpresaIngles = Request.Params["porteEmpresa"] != null ? Request.Params["porteEmpresa"].ToString() : "";
            faturamentoEmpresaIngles = Request.Params["faturamentoEmpresa"] != null ? Request.Params["faturamentoEmpresa"].ToString() : "";
            modalidadeEmpresaIngles = Request.Params["modalidadeEmpresa"] != null ? Request.Params["modalidadeEmpresa"].ToString() : "";
            objetivoEmpresaIngles = Request.Params["objetivoEmpresa"] != null ? Request.Params["objetivoEmpresa"].ToString() : "";
            perfilEmpresaIngles = Request.Params["perfilEmpresa"] != null ? Request.Params["perfilEmpresa"].ToString() : "";
            aprovacao = "Sim";
            setLinkImprimir();

            if (!IsPostBack)
                SalvarItensPesquisa();
        }

        private void SalvarItensPesquisa()
        {
            SPListItemCollection myList = site.Lists["Relatório de Consulta"].Items;
            
            if (!estado.Equals(""))
            {
                SPListItem myItem = myList.Add();
                myItem["Estado Origem"] = estadoPesquisa;// Request.QueryString["Estado Atual"];
                myItem["Municipio Origem"] = municipioPesquisa;// Request.QueryString["Municipio Atual"];
                myItem["Estado"] = estado;// Request.QueryString["estadoEmpresa"];
                myItem["Municipio"] = municipio;// Request.QueryString["municipioEmpresa"];
                myItem["Setor"] = setorEmpresa; // Request.QueryString["setorEmpresa"];
                myItem["Número de Funcionários"] = porteEmpresa; // Request.QueryString["porteEmpresa"];
                myItem["Faturamento Anual"] = faturamentoEmpresa; // Request.QueryString["faturamentoEmpresa"];
                myItem["Modalidade"] = modalidadeEmpresa; // Request.QueryString["modalidadeEmpresa"];
                myItem["Finalidade"] = objetivoEmpresa; // Request.QueryString["objetivoEmpresa"];
                myItem["Perfil"] = perfilEmpresa; // Request.QueryString["objetivoEmpresa"];

                site.AllowUnsafeUpdates = true;
                myItem.Update();
                site.AllowUnsafeUpdates = false;
            }
        }   

        //OBJECT'S FORM.
        protected void lbImprimir_Click(object sender, EventArgs e)
        {
            Response.Redirect(string.Format(@"imprimiResultado_ingles.aspx?estadoEmpresa={0}municipioEmpresa={1}&setorEmpresa={2}&porteEmpresa={3}&faturamentoEmpresa={4}&modalidadeEmpresa={5}&objetivoEmpresa={6}", estado, municipio, setorEmpresa, porteEmpresa, faturamentoEmpresa, modalidadeEmpresa, objetivoEmpresa));
        }

        protected void lbExportar_Click(object sender, EventArgs e)
        {
            try
            {            
                SPQuery p = new SPQuery();
                //p.Query = string.Format(@"<Where><And><Contains><FieldRef Name='TipoModulo' /><Value Type='Lookup'>{0}</Value></Contains><And><Contains><FieldRef Name='Finalidade' /><Value Type='LookupMulti'>{1}</Value></Contains><And><Contains><FieldRef Name='FaturamentoAnual' /><Value Type='LookupMulti'>{2}</Value></Contains><And><Contains><FieldRef Name='Aprovacao' /><Value Type='Text'>Sim</Value></Contains><And><Contains><FieldRef Name='SetorEconomico' /><Value Type='LookupMulti'>{3}</Value></Contains><And><Contains><FieldRef Name='UF' /><Value Type='LookupMulti'>{4}</Value></Contains><Contains><FieldRef Name='NumeroEmpregados' /><Value Type='LookupMulti'>{5}</Value></Contains></And></And></And></And></And></And></Where>", modalidadeEmpresa, objetivoEmpresa, faturamentoEmpresa, setorEmpresa, estado, porteEmpresa);
                
                String query = "<Where><And><Contains><FieldRef Name='TipoModulo' /><Value Type='Lookup'>" + modalidadeEmpresa + "</Value></Contains>";
                query += "<And><Contains><FieldRef Name='Finalidade' /><Value Type='LookupMulti'>" + objetivoEmpresa + "</Value></Contains>";
                query += "<And><Contains><FieldRef Name='FaturamentoAnual' /><Value Type='LookupMulti'>" + faturamentoEmpresa + "</Value></Contains>";
                query += "<And><Contains><FieldRef Name='SetorEconomico' /><Value Type='LookupMulti'>" + setorEmpresa + "</Value></Contains>";
                query += "<And><Contains><FieldRef Name='NumeroEmpregados' /><Value Type='LookupMulti'>" + porteEmpresa + "</Value></Contains>";
                if (estado == "Todos") { }
                else if (estado != "Todos" && municipio == "Todos")
                {
                    query += "<And><Contains><FieldRef Name='Municipio' /><Value Type='LookupMulti'>" + estado + "</Value></Contains>";
                }
                else if (estado != "Todos" && municipio != "Todos")
                {
                    query += "<And><Or><Contains><FieldRef Name='Municipio' /><Value Type='LookupMulti'>Todos/" + estado + "</Value></Contains>";
                    query += "         <Contains><FieldRef Name='Municipio' /><Value Type='LookupMulti'>" + municipio + "</Value></Contains></Or>";
                }
                query += "<Contains><FieldRef Name='Aprovacao' /><Value Type='LookupMulti'>" + aprovacao + "</Value></Contains>";
                query += "</And></And></And></And></And></And></Where>";

                p.Query = query;
                //Request.QueryString[

                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (SPSite ElevatedSiteCollection = new SPSite(siteColl.ID))
                    {
                        using (SPWeb ElevatedSite = ElevatedSiteCollection.OpenWeb(site.ID))
                        {
                            //Creando el emcabezado de 
                            StringBuilder htmlEncabecadoDaLista = new StringBuilder();

                            string styleColorDados = "background:#dce6f1";
                            string styleColorHead = "background:#8db4e2";
                            string htmlToExcel = string.Empty;
                            htmlEncabecadoDaLista.Append(string.Format(@"<table cellpadding='0' cellspacing='0' class='ImprimeResumo'>
<tr>
	<td><b>You Have Selected:</b></td>
    <td></td>
</tr>
<tr>
	<td></td>
    <td></td>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Date</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label>{7}</label></td>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Your State</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxUF'>{0}</label>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Your County</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxMunicipio'>{1}</label>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Your Industry</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxSetor'>{2}</label>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Your Porte</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxPorte'>{3}</label>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Annual Revenue</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxFaturamento'>{4}</label>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>Need</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxFinalidade'>{5}</label>
</tr>
<tr align='left'>
	<td style='border: thin solid #C0C0C0;{8}'><b>To</b></td>
    <td style='border: thin solid #C0C0C0;{9}'><label id='boxObjetivo'>{6}</label>
</tr>
<tr>
	<td></td>
    <td></td>
</tr>
<tr>
	<td><b>Results of your query:</b></td>
    <td></td>
</tr>
<tr>
	<td></td>
    <td></td>
</tr>
<tr><td style='border: thin solid #C0C0C0;{8}'><b>Coverage</b></td><td style='border: thin solid #C0C0C0;{8}'><b>Manager</b></td><td style='border: thin solid #C0C0C0;{8}'><b>Line/Program</b></td><td style='border: thin solid #C0C0C0;{8}'><b>Description</b></td></tr>", estadoIngles, municipioIngles, setorEmpresaIngles, porteEmpresaIngles, faturamentoEmpresaIngles, modalidadeEmpresaIngles, objetivoEmpresaIngles, (DateTime.Now.ToShortDateString().ToString() + " às " + DateTime.Now.ToShortTimeString().ToString() + " hs."), styleColorHead, styleColorDados));

                            string HtmlDadosDaLista = string.Empty;

                            //SPList list = ElevatedSite.Lists[new Guid("Modulo")];

                            SPList list = site.Lists["Módulos"];
                            SPListItemCollection colecction = list.GetItems(p); // teste.GetItems(p);

                            //DataTable table = new DataTable();
                            //table.Columns.Add("Gestor", typeof(string));
                            //table.Columns.Add("LinhaPrograma", typeof(string));
                            //table.Columns.Add("Descricao", typeof(string)); //, typeof(NumberField));
                            //table.Rows.Add("Data da consulta : " + DateTime.Now.ToShortDateString().ToString() + " " + DateTime.Now.ToShortTimeString().ToString());

                            //DataRow newRow;
                            //GridView gv = new GridView();
                            //gv.AlternatingRowStyle.BackColor = Color.PaleGreen;

                            int contadorLinha = 0;
                            string corLinha = string.Empty;
                            foreach (SPListItem item in colecction)
                            {
                                //newRow = table.Rows.Add();
                                //newRow["Gestor"] = item["Gestor"] != null ? item["Gestor"].ToString() : "";
                                //newRow["LinhaPrograma"] = item["LinhaPrograma"] != null ? item["LinhaPrograma"].ToString() : "";
                                //newRow["Descricao"] = item["Descricao"] != null ? item["Descricao"].ToString() : "";
                                string abrangenciaGeografica = item["AbrangenciaGeografica"] != null ? item["AbrangenciaGeografica"].ToString() : "";
                                string gestor = item["Gestor"] != null ? item["Gestor"].ToString() : "";
                                string linhaPrograma = item["LinhaPrograma"] != null ? item["LinhaPrograma"].ToString() : "";
                                string descricao = item["DescricaoIngles"] != null ? item["DescricaoIngles"].ToString() : "";
                                corLinha = ((contadorLinha++ % 2) == 0) ? styleColorDados : "";

                                htmlEncabecadoDaLista.Append(string.Format(@"<tr><td style='border: thin solid #C0C0C0;{3}'>{0}</td><td style='border: thin solid #C0C0C0;{3}'>{1}</td><td style='border: thin solid #C0C0C0;{3}'>{2}</td><td style='border: thin solid #C0C0C0;{3}'>{3}</td></tr>", abrangenciaGeografica, gestor, linhaPrograma, descricao, corLinha));
                                //HtmlDadosDaLista += string.Format(@"<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>", gestor, linhaPrograma, descricao);

                            }

                            //SPBoundField boundField = new SPBoundField();
                            //boundField.HeaderText = "Gestor";
                            //boundField.HeaderStyle.ForeColor = Color.White;
                            //boundField.HeaderStyle.BackColor = Color.RoyalBlue;
                            //boundField.HeaderStyle.Font.Bold = true;
                            //boundField.DataField = "Gestor";
                            //gv.Columns.Add(boundField);

                            //boundField = new SPBoundField();
                            //boundField.HeaderText = "Linha/Programa";
                            //boundField.HeaderStyle.ForeColor = Color.White;
                            //boundField.HeaderStyle.BackColor = Color.RoyalBlue;
                            //boundField.HeaderStyle.Font.Bold = true;
                            //boundField.DataField = "LinhaPrograma";
                            //gv.Columns.Add(boundField);

                            //boundField = new SPBoundField();
                            //boundField.HeaderText = "Descrição";
                            //boundField.HeaderStyle.ForeColor = Color.White;
                            //boundField.HeaderStyle.BackColor = Color.RoyalBlue;
                            //boundField.HeaderStyle.Font.Bold = true;
                            //boundField.DataField = "Descricao";
                            //boundField.ControlStyle.Width = new Unit(120);
                            //gv.Columns.Add(boundField);

                            //gv.AutoGenerateColumns = false;
                            //gv.DataSource = table.DefaultView;
                            //gv.DataBind();
                            //gv.AllowSorting = false;

                             htmlEncabecadoDaLista.Append("</table>");
                            //htmlToExcel = htmlEncabecadoDaLista + HtmlDadosDaLista + "</table>";

                            HttpContext.Current.Response.ClearContent();
                            HttpContext.Current.Response.ClearHeaders();
                            string attachment = "attachment; filename=Consulta" + "(" + DateTime.Now.ToShortDateString().ToString().Replace("/", "_") + "-" + DateTime.Now.ToShortTimeString().ToString().Replace(":", "_") + ")" + ".xls";
                            HttpContext.Current.Response.AddHeader("content-disposition", attachment);
                            HttpContext.Current.Response.ContentType = "application/excel";
                            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; // System.Text.Encoding.UTF8;

                            StringWriter sw = new StringWriter(htmlEncabecadoDaLista);
                            HtmlTextWriter htw = new HtmlTextWriter(sw);
                            
                            //gv.RenderControl(htw);
                            
                            HttpContext.Current.Response.Write(sw.ToString());
                            HttpContext.Current.Response.Flush();
                            HttpContext.Current.Response.Close();
                            HttpContext.Current.Response.End();
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                StringWriter sw = new StringWriter();
                HtmlTextWriter htw = new HtmlTextWriter(sw);
                HttpContext.Current.Response.Write(ex.Message);
            }
        }

        //METHOD'S FORM.
        private void setLinkImprimir()
        {
            string linkImprimir = string.Format(@"{7}/SitePages/imprimiResultado_ingles.aspx?estadoEmpresa={0}&municipioEmpresa={1}&setorEmpresa={2}&porteEmpresa={3}&faturamentoEmpresa={4}&modalidadeEmpresa={5}&objetivoEmpresa={6}", Server.UrlEncode(estado), Server.UrlEncode(municipio), Server.UrlEncode(setorEmpresa), Server.UrlEncode(porteEmpresa), Server.UrlEncode(faturamentoEmpresa), Server.UrlEncode(modalidadeEmpresa), Server.UrlEncode(objetivoEmpresa), site.Url.ToString());
            lnkprint.HRef = linkImprimir;
        }

    }
        }
    

